package com.serviceprovider.business.module.admin.user.dao.impl;

import com.serviceprovider.business.module.admin.role.model.Role;
import com.serviceprovider.business.module.admin.user.dao.interfaces.IUserDao;
import com.serviceprovider.business.module.common.Page;
import com.serviceprovider.business.module.common.PaginationHelper;
import com.serviceprovider.business.module.user.model.impl.User;
import com.serviceprovider.business.module.user.model.impl.UserSearchCriteria;
import com.serviceprovider.business.utility.AppUtil;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;

import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.*;

import javax.sql.rowset.Joinable;

/**
 * @Leonardo A. Auer
 *
 * @since 04/11/2011
 */
@Repository
public class UserDao extends SimpleJdbcDaoSupport implements IUserDao {

    @Qualifier("userJdbcInsert")
    @Autowired
    private SimpleJdbcInsert jdbcInsert;

    @Autowired
    public UserDao(@Qualifier("jdbcTemplate") JdbcTemplate jdbcTemplate) {
        setJdbcTemplate(jdbcTemplate);
    }

    @Override
    public Page<User> findUserByRole(Role role, int pageNumber, int recordSize,
            String sortColumn, String sortOrder) {
        if (role == null) {
            throw new IllegalArgumentException("Role parameter is mandatory");
        }

        PaginationHelper<User> ph = new PaginationHelper<User>();
        String sqlCount = " SELECT count(*) FROM user where role = "
                + role.getId();
        String sqlQuery = " SELECT * FROM user WHERE role = " + role.getId()
                + "  order by " + sortColumn + " " + sortOrder;
        Object[] args = {};

        return ph.fetchPage(getJdbcTemplate(), sqlCount, sqlQuery, args,
                pageNumber, recordSize, sortColumn, sortOrder,
                new UserMapper());
    }

    @Override
    public User findUserById(int userId) {

        List<User> userList = getSimpleJdbcTemplate().query(
                " SELECT * FROM user WHERE id = ? ", new UserMapper(), userId);

        User user = null;
        if (userList.size() != 0) {
            user = userList.get(0);
        }

        return user;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<User> findUsersById(Collection<Integer> ids) {
        String sqlQuery = " SELECT * FROM user WHERE id IN ("
                + AppUtil.getSqlIN(ids) + ")";
        return getJdbcTemplate().query(sqlQuery, new UserMapper());
    }

    @Override
    public Page<User> findUsersById(List<Integer> ids, int pageNumber,
            int recordSize, String sortColumn, String sortOrder) {
        if (ids == null || ids.size() == 0) {
            throw new IllegalArgumentException(
                    "Id's list can't be null or empty");
        }

        PaginationHelper<User> ph = new PaginationHelper<User>();
        String sqlCount = " SELECT count(*) FROM user where id in ("
                + AppUtil.getSqlIN(ids) + " )";
        String sqlQuery = " SELECT * FROM user WHERE id IN ("
                + AppUtil.getSqlIN(ids) + ")  order by " + sortColumn + " "
                + sortOrder;
        Object[] args = {};

        return ph.fetchPage(getJdbcTemplate(), sqlCount, sqlQuery, args,
                pageNumber, recordSize, sortColumn, sortOrder,
                new UserMapper());
    }

    @Override
    public User findUserByEFIN(int efin) {

        List<User> userList = getSimpleJdbcTemplate().query(
                " SELECT * FROM user WHERE efin = ? ", new UserMapper(), efin);

        User user = null;
        if (userList.size() != 0) {
            user = userList.get(0);
        }

        return user;
    }

    @Override
    public User findUserBySSN(int ssn) {

        List<User> userList = getSimpleJdbcTemplate().query(
                " SELECT * FROM user WHERE ssn = ? ", new UserMapper(), ssn);

        User user = null;
        if (userList.size() != 0) {
            user = userList.get(0);
        }

        return user;
    }

    @Override
    public User findUserByUsername(String username) {

        List<User> userList = getSimpleJdbcTemplate().query(
                " SELECT * FROM user WHERE email = ? ", new UserMapper(),
                username);

        User user = null;
        if (userList.size() != 0) {
            user = userList.get(0);
        }

        return user;
    }

    @Override
    public Page<User> findUserByCity(String city, int pageNumber,
            int recordSize, String sortColumn, String sortOrder) {
        PaginationHelper<User> ph = new PaginationHelper<User>();
        String sqlCount = " SELECT COUNT(*) FROM user u, address a WHERE u.contactAddressId = a.id  and a.city = '"
                + city + "'";
        String sqlQuery = new StringBuffer().append(" SELECT u.* FROM user u, address a ").append(" WHERE u.contactAddressId = a.id ").append(" AND a.city = '").append(city).append("'").toString();

        Object[] args = {};

        return ph.fetchPage(getJdbcTemplate(), sqlCount, sqlQuery + " order by "
                + sortColumn + " " + sortOrder, args, pageNumber,
                recordSize, sortColumn, sortOrder, new UserMapper());
    }

    @Override
    public Page<User> findUserByZipCode(String zipCode, int pageNumber,
            int recordSize, String sortColumn, String sortOrder) {
        PaginationHelper<User> ph = new PaginationHelper<User>();
        String sqlCount = " SELECT COUNT(*) FROM user u, address a WHERE u.contactAddressId = a.id  and a.zip ='"
                + zipCode + "'";
        String sqlQuery = new StringBuffer().append(" SELECT u.* FROM user u, address a ").append(" WHERE u.contactAddressId = a.id ").append(" AND a.zip = '").append(zipCode).append("'").toString();

        Object[] args = {};

        return ph.fetchPage(getJdbcTemplate(), sqlCount, sqlQuery + " order by "
                + sortColumn + " " + sortOrder, args, pageNumber,
                recordSize, sortColumn, sortOrder, new UserMapper());
    }

    @Override
    public Page<User> findUserByCounty(String county, int pageNumber,
            int recordSize, String sortColumn, String sortOrder) {
        PaginationHelper<User> ph = new PaginationHelper<User>();
        String sqlCount = " SELECT COUNT(*) FROM user u, address a WHERE u.contactAddressId = a.id and a.county = '"
                + county + "'";
        String sqlQuery = new StringBuffer().append(" SELECT * FROM user u, address a ").append(" WHERE u.contactAddressId = a.id ").append(" AND a.country = '").append(county).append("'").toString();

        Object[] args = {};

        return ph.fetchPage(getJdbcTemplate(), sqlCount, sqlQuery + " order by "
                + sortColumn + " " + sortOrder, args, pageNumber,
                recordSize, sortColumn, sortOrder, new UserMapper());
    }

    @Override
    public Page<User> findUserByCountry(String country, int pageNumber,
            int recordSize, String sortColumn, String sortOrder) {

        PaginationHelper<User> ph = new PaginationHelper<User>();
        String sqlCount = " SELECT COUNT(*) FROM user u, address a WHERE u.contactAddressId = a.id ";
        String sqlQuery = new StringBuffer().append(" SELECT * FROM user u, address a ").append(" WHERE u.contactAddressId = a.id ").append(" AND a.country = '").append(country).append("'").toString();

        Object[] args = {};

        return ph.fetchPage(getJdbcTemplate(), sqlCount, sqlQuery + " order by "
                + sortColumn + " " + sortOrder, args, pageNumber,
                recordSize, sortColumn, sortOrder, new UserMapper());
    }

    @Override
    public User findUserByUsernamePassword(String firstName, String middleName,
            String lastName, String password) {

        List<User> userList = null;
        User user = null;

        StringBuffer query = new StringBuffer().append(" SELECT * FROM user WHERE firstName = ? ").append(" AND lastName = ? ").append(" AND password = ? ");

        if (middleName != null && !middleName.trim().equalsIgnoreCase("")) {
            query.append(" AND milddleName = ? ");
            userList = getSimpleJdbcTemplate().query(query.toString(), new UserMapper(), firstName,
                    lastName, password, middleName);
        } else {
            userList = getSimpleJdbcTemplate().query(query.toString(),
                    new UserMapper(), firstName, lastName, password);
        }

        if (userList.size() != 0) {
            user = userList.get(0);
        }

        return user;
    }

    @Override
    public Page<User> findUserByCriteria(UserSearchCriteria criteria,
            int pageNumber, int recordSize, String sortColumn, String sortOrder) {
        if (criteria == null) {
            throw new IllegalArgumentException("Criteria is a mandatory value");
        }

        boolean joinAddress = !StringUtils.isEmpty(criteria.getCity())
                || !StringUtils.isEmpty(criteria.getCountry().getCode())
                || !StringUtils.isEmpty(criteria.getZip());
        boolean joinQuestion = !StringUtils.isEmpty(criteria.getRefNumber());

        StringBuffer query = new StringBuffer();
        StringBuffer sqlCount = new StringBuffer();
        StringBuffer sqlGroup = new StringBuffer();
        if (joinAddress) {
            query.append(" SELECT u.* FROM user u, address a ");
            if (joinQuestion) {
                query.append(" ,question q");
            }
            query.append(" WHERE u.contactAddressId = a.id ");
            if (joinQuestion) {
                query.append(" and q.email=u.email");
            }
            sqlCount.append(" SELECT COUNT(*) FROM user u, address a ");
            if (joinQuestion) {
                sqlCount.append(",question q");

            }
            sqlCount.append(" WHERE u.contactAddressId = a.id ");
            if (joinQuestion) {
                sqlCount.append(" and q.email=u.email");

            }
        } else {
            query.append(" SELECT * FROM user u ");
            if (joinQuestion) {
                query.append(" ,question q");
            }
            query.append(" WHERE 1 = 1 ");
            if (joinQuestion) {
                query.append(" and q.email=u.email");
            }
            sqlCount.append(" SELECT COUNT(*) FROM user u ");
            if (joinQuestion) {
                sqlCount.append(",question q");

            }
            sqlCount.append(" WHERE 1 = 1 ");
            if (joinQuestion) {
                sqlCount.append(" and q.email=u.email");

            }
        }

        List<String> subQuery = new ArrayList<String>();
        doSqlString("u.email", criteria.getEmail(), subQuery, true);
        doSqlString("u.alternateEmail", criteria.getAlternateEmail(), subQuery,
                true);
        doSqlString("u.password", criteria.getPassword(), subQuery, false);
        doSqlString("u.enabled", criteria.isEnabled() ? 1 : 0, subQuery);
        doSqlString("u.deleted", criteria.isDeleted() ? 1 : 0, subQuery);
        doSqlString("u.role", criteria.getRoleId(), subQuery);
        doSqlString("u.refererId", criteria.getRefererId(), subQuery);
        doSqlString("u.level_1_UserID", criteria.getLevel1UserID(), subQuery);
        doSqlString("u.firstName", criteria.getFirstName(), subQuery, true);
        doSqlString("u.milddleName", criteria.getMilddleName(), subQuery, true);
        doSqlString("u.lastName", criteria.getLastName(), subQuery, true);
        doSqlString("u.efin",
                criteria.getEfin() == null ? 0 : criteria.getEfin(), subQuery);
        doSqlString("u.ssn", criteria.getSsn() == null ? 0 : criteria.getSsn(),
                subQuery);
        doSqlString("u.ptin",
                criteria.getPtin() == null ? 0 : criteria.getPtin(), subQuery);
        doSqlString("u.businessName", criteria.getBusinessName(), subQuery,
                true);
        doSqlString("u.contactAddressId", criteria.getContactAddressId(),
                subQuery);
        doSqlString("u.businessAddressId", criteria.getBusinessAddressId(),
                subQuery);
        doSqlString("u.shippingAddressId", criteria.getShippingAddressId(),
                subQuery);
        doSqlString("u.prefMessageNotifyType",
                criteria.getPrefMessageNotifyType(), subQuery);
        doSqlString("u.prefMessageNotifyAlert",
                criteria.getPrefMessageNotifyAlert(), subQuery);
        doSqlString("u.bonusTestAmount", criteria.getBonusTestAmount(),
                subQuery);
        doSqlString("u.businessName", criteria.getBusinessName(), subQuery,
                true);
        doSqlString("u.inGui", criteria.getInGui(), subQuery);

        if (joinAddress) {
            doSqlString("a.city", criteria.getCity(), subQuery, true);
            doSqlString("a.country", criteria.getCountry().getCode(), subQuery,
                    true);
            doSqlString("a.zip", criteria.getZip(), subQuery, true);
        }
        if (joinQuestion) {
            doSqlString("q.ref_num", criteria.getRefNumber(), subQuery, true);

        }
        if (criteria.getCreatedOn() != null) {
            SimpleDateFormat sdf = new SimpleDateFormat("mm/DD/yyyy");
            doSqlString("createdOn", sdf.format(criteria.getCreatedOn()),
                    subQuery, false);
        }
        if (joinQuestion) {
            sqlGroup.append(" group by u.id");

        }
        for (String aSubQuery : subQuery) {
            query.append(aSubQuery);
            sqlCount.append(aSubQuery);
        }

        PaginationHelper<User> ph = new PaginationHelper<User>();
        Object[] args = {};
        return ph.fetchPage(getJdbcTemplate(), sqlCount.toString(),
                query.toString() + sqlGroup.toString() + " order by " + sortColumn + " " + sortOrder,
                args, pageNumber, recordSize, sortColumn, sortOrder,
                new UserMapper());
    }

    private void doSqlString(String column, String value,
            List<String> subQuery, boolean useLike) {
        StringBuilder result = new StringBuilder();

        if (value == null || value.trim().equalsIgnoreCase("")) {
            return;
        }

        result.append(" AND ");
        result.append(column);
        result.append(useLike ? " LIKE '%" : " = '");
        result.append(value.trim());
        result.append(useLike ? "%'" : "' ");

        subQuery.add(result.toString());
    }

    private void doSqlString(String column, int value, List<String> subQuery) {

        if (value == 0) {
            return;
        }

        StringBuilder result = new StringBuilder();

        result.append(" AND ");
        result.append(column);
        result.append(" = ");
        result.append(value);
        result.append(" ");

        subQuery.add(result.toString());
    }

    @Override
    public Page<User> findAllUsers(int pageNumber, int recordSize,
            String sortColumn, String sortOrder) {
        PaginationHelper<User> ph = new PaginationHelper<User>();
        String sqlCount = " SELECT count(*) FROM user ";
        String sqlQuery = " SELECT * FROM user " + " order by " + sortColumn
                + " " + sortOrder;
        Object[] args = {};

        return ph.fetchPage(getJdbcTemplate(), sqlCount, sqlQuery, args,
                pageNumber, recordSize, sortColumn, sortOrder,
                new UserMapper());
    }

    @Override
    public User insertUser(User user) {

        if (user == null) {
            throw new IllegalArgumentException("User can't be null");
        }

        if (user.getEmail() == null || user.getEmail().trim().equals("")) {
            throw new IllegalArgumentException(
                    "User's email is a mandatory value");
        }

        if (user.getPassword() == null || user.getPassword().trim().equals("")) {
            throw new IllegalArgumentException(
                    "User's password is a mandatory value");
        }

        if (user.getRole() == null) {
            throw new IllegalArgumentException(
                    "User's role is a mandatory value");
        }

        if (user.getFirstName() == null
                || user.getFirstName().trim().equals("")) {
            throw new IllegalArgumentException(
                    "User's first name is a mandatory value");
        }

        if (user.getLastName() == null || user.getLastName().trim().equals("")) {
            throw new IllegalArgumentException(
                    "User's last name is a mandatory value");
        }

        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("id", user.getId());
        parameters.put("email", user.getEmail());
        parameters.put("alternateEmail", user.getAlternateEmail());
        parameters.put("password", user.getPassword());
        parameters.put("enabled", user.isEnabled() ? 1 : 0);
        parameters.put("deleted", user.isDeleted() ? 1 : 0);
        parameters.put("role", user.getRole().getId());
        parameters.put("referer_id", user.getRefererId());
        parameters.put("firstName", user.getFirstName());
        parameters.put("milddleName", user.getMilddleName());
        parameters.put("lastName", user.getLastName());
        parameters.put("efin", user.getEfin());
        parameters.put("ssn", user.getSsn());
        parameters.put("ptin", user.getPtin());
        parameters.put("inGui", 1);
        parameters.put("businessName", user.getBusinessName());
        parameters.put("businessFein", user.getBusinessFein());
        parameters.put("ownerssn", user.getOwnerSsn());
        parameters.put("pref_message_notify_type",
                user.getPrefMessageNotifyType());
        parameters.put("pref_message_notify_alert",
                user.getPrefMessageNotifyAlert());
        parameters.put("bonusTestAmount", user.getBonusTestAmount());

        if (user.getContactAddress() != null) {
            parameters.put("contactAddressId", user.getContactAddress().getId());
        }
        if (user.getBusinessAddress() != null) {
            parameters.put("businessAddressId", user.getBusinessAddress().getId());
        }
        if (user.getShippingAddress() != null) {
            parameters.put("shippingAddressId", user.getShippingAddress().getId());
        }
        if (user.getLevel1User() != null) {
            parameters.put("Level_1_UserID", user.getLevel1User().getId());
        }
        if (user.getCreatedOn() != null) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            parameters.put("createdOn",
                    Timestamp.valueOf(sdf.format(user.getCreatedOn())));
        }

        Number id = jdbcInsert.executeAndReturnKey(parameters);
        user.setId(id.intValue());

        return user;
    }

    @Override
    public void updateUser(User user) {

        if (user == null) {
            throw new IllegalArgumentException("User can't be null");
        }

        if (user.getEmail() == null || user.getEmail().trim().equals("")) {
            throw new IllegalArgumentException("User's email can't be null");
        }

        if (user.getRole() == null) {
            throw new IllegalArgumentException(
                    "User's role is a mandatory value");
        }

        if (user.getFirstName() == null
                || user.getFirstName().trim().equals("")) {
            throw new IllegalArgumentException(
                    "User's first name can't be null");
        }

        if (user.getLastName() == null || user.getLastName().trim().equals("")) {
            throw new IllegalArgumentException("User's last name can't be null");
        }

        String query = new StringBuffer().append(" UPDATE user SET ").append(" email = ?, ").append(" alternateEmail = ?, ").append(" enabled = ?, ").append(" deleted = ?, ").append(" role = ?, ").append(" referer_id = ?, ").append(" level_1_userid = ?, ").append(" firstName = ?, ").append(" milddleName = ?, ").append(" lastName = ?, ").append(" efin = ?, ").append(" ssn = ?, ").append(" ptin = ?, ").append(" businessName = ?, ").append(" businessFein = ?, ").append(" ownerssn = ?, ").append(" orderApproval = ?, ").append(" checkPrintingBy = ?, ").append(" contactAddressId = ?, ").append(" businessAddressId = ?, ").append(" shippingAddressId = ?, ").append(" pref_message_notify_type = ?, ").append(" pref_message_notify_alert = ?, ").append(" bonusTestAmount = ?, ").append(" createdOn = ? ").append(" WHERE id = ? ").toString();

        getSimpleJdbcTemplate().update(
                query,
                user.getEmail(),
                user.getAlternateEmail(),
                user.isEnabled(),
                user.isDeleted(),
                user.getRole().getId(),
                user.getRefererId(),
                user.getLevel1User() != null ? user.getLevel1User().getId()
                : null,
                user.getFirstName(),
                user.getMilddleName(),
                user.getLastName(),
                user.getEfin(),
                user.getSsn(),
                user.getPtin(),
                user.getBusinessName(),
                user.getBusinessFein(),
                user.getOwnerSsn(),
                user.getOrderApproval(),
                user.getCheckPrintingBy(),
                user.getContactAddress() != null ? user.getContactAddress().getId() : null,
                user.getBusinessAddress() != null ? user.getBusinessAddress().getId() : null,
                user.getShippingAddress() != null ? user.getShippingAddress().getId() : null, user.getPrefMessageNotifyType(),
                user.getPrefMessageNotifyAlert(), user.getBonusTestAmount(),
                user.getCreatedOn(), user.getId());
    }

    @Override
    public void deleteUser(int userId) {
        String query = new StringBuffer().append(" UPDATE user SET deleted = 1 ").append(" WHERE id = ? ").toString();

        getSimpleJdbcTemplate().update(query, userId);
    }

    @Override
    public void undeteleUser(int userId) {
        String query = new StringBuffer().append(" UPDATE user SET deleted = 0 ").append(" WHERE id = ? ").toString();

        getSimpleJdbcTemplate().update(query, userId);
    }

    @Override
    public void updateActiveStatus(int userId, boolean status) {
        String query = new StringBuffer().append(" UPDATE user SET enabled = ? ").append(" WHERE id = ? ").toString();

        getSimpleJdbcTemplate().update(query, status, userId);
    }

    @Override
    public void updatePassword(int userId, String password) {
        String query = new StringBuffer().append(" UPDATE user SET password = ? ").append(" WHERE id = ? ").toString();

        getSimpleJdbcTemplate().update(query, password, userId);
    }

    @Override
    public void deleteUser(List<Integer> usersId) {

        if (usersId == null) {
            throw new IllegalArgumentException("User's id list can't be null");
        }

        String query = new StringBuffer().append(" UPDATE user SET deleted = 1 ").append(" WHERE id IN ( ").append(AppUtil.getSqlIN(usersId)).append(" ) ").toString();

        getSimpleJdbcTemplate().update(query);
    }

    @Override
    public void undeteleUser(List<Integer> usersId) {

        if (usersId == null) {
            throw new IllegalArgumentException("User's id list can't be null");
        }

        String query = new StringBuffer().append(" UPDATE user SET deleted = 0 ").append(" WHERE id IN ( ").append(AppUtil.getSqlIN(usersId)).append(" ) ").toString();

        getSimpleJdbcTemplate().update(query);
    }

    @Override
    public void updateActiveStatus(List<Integer> usersId, boolean status) {

        if (usersId == null) {
            throw new IllegalArgumentException("User's id list can't be null");
        }

        String query = new StringBuffer().append(" UPDATE user SET enabled = ? ").append(" WHERE id IN ( ").append(AppUtil.getSqlIN(usersId)).append(" ) ").toString();

        getSimpleJdbcTemplate().update(query, status);
    }

    public void updateInGuiFlag(List<Integer> usersId, boolean status) {

        if (usersId == null) {
            throw new IllegalArgumentException("User's id list can't be null");
        }

        String query = new StringBuffer().append(" UPDATE user SET inGui = ? ").append(" WHERE id IN ( ").append(AppUtil.getSqlIN(usersId)).append(" ) ").toString();

        getSimpleJdbcTemplate().update(query, status);
    }

    @Override
    public Page<User> findUsersByLevel1User(int level1UserId, int pageNumber,
            int recordSize, String sortColumn, String sortOrder) {
        PaginationHelper<User> ph = new PaginationHelper<User>();
        String sqlCount = " SELECT count(*) FROM user WHERE level_1_userid = "
                + level1UserId;
        String sqlQuery = " SELECT * FROM user WHERE level_1_userid = "
                + level1UserId + " order by " + sortColumn + " " + sortOrder;
        Object[] args = {};

        return ph.fetchPage(getJdbcTemplate(), sqlCount, sqlQuery, args,
                pageNumber, recordSize, sortColumn, sortOrder,
                new UserMapper());
    }

    @Override
    public Page<User> findUsersByRoleAndLevel1User(int role, int level1UserId,
            int pageNumber, int recordSize, String sortColumn, String sortOrder) {
        PaginationHelper<User> ph = new PaginationHelper<User>();
        String sqlCount = " SELECT count(*) FROM user WHERE level_1_userid = "
                + level1UserId + " AND role = " + role;
        String sqlQuery = new StringBuffer().append(" SELECT * FROM user WHERE level_1_userid = ").append(level1UserId).append(" AND inGui !=0 AND role = ").append(role).append(" order by " + sortColumn + " " + sortOrder).toString();
        Object[] args = {};

        return ph.fetchPage(getJdbcTemplate(), sqlCount, sqlQuery, args,
                pageNumber, recordSize, sortColumn, sortOrder,
                new UserMapper());
    }

    @Override
    public void retrieveLevel1User(List<User> userList) {
        // making a list of level1User id
        List<Integer> level1UserIdList = new ArrayList<Integer>();
        for (User user : userList) {
            if (user.getLevel1User() != null) {
                level1UserIdList.add(user.getLevel1User().getId());
            }
        }

        // finding all level1User users from the list level1UserIdList
        List<User> userLevel1List = this.findUsersById(level1UserIdList);

        // Creating a map from userLevel1List - key:level1userId
        // value:level1IdUser
        Map<Integer, User> level1userMap = new HashMap<Integer, User>();
        for (User level1user : userLevel1List) {
            level1userMap.put(level1user.getId(), level1user);
        }

        // Linking Users with its level1Users
        for (User user : userList) {
            if (user.getLevel1User() != null) {
                user.setLevel1User(level1userMap.get(user.getLevel1User().getId()));
            }
        }
    }

    public void retrieveLevel1User(User user) {
        if (user.getLevel1User() == null) {
            return;
        }
        if (user.getLevel1User().getId() == null) {
            return;
        }
        user.setLevel1User(this.findUserById(user.getLevel1User().getId()));
    }

    @Override
    public List<Long> getAllNonMatchingIds(Integer roleId) {

        String sql = "SELECT distinct(us.id) FROM user us where us.role="
                + roleId
                + " and us.id not in (select distinct(ma.original_id) FROM sp_id_mappings ma)";
        Map row = null;
        List list = getJdbcTemplate().queryForList(sql);
        ArrayList<Long> dataList = new ArrayList<Long>();
        for (Object obj : list) {
            row = (Map) obj;
            dataList.add((Long) row.get("id"));
        }
        return dataList;

    }

    @Override
    public void updateAllUsersActiveFlag(Integer roleId, Boolean isActive) {
        getSimpleJdbcTemplate().update(
                "UPDATE user SET enabled = ? " + "WHERE role = ?", isActive,
                roleId);
    }
}
